﻿Imports System.Data.SqlClient
Imports System.Data
Imports System.Reflection
Imports MySql.Data.MySqlClient

Public Class BancoDeDados
  Private Const CONNSTRING As String = "Server=200.155.77.194;uid=evosystems;pwd=evosenha;Database=extranet;Allow User Variables=True"
  'Private Const CONNSTRING As String = "Server=200.234.202.108;uid=evosystems;pwd=evosenha;Database=evosystems;Allow User Variables=True"
  Private Shared objConexao As MySqlConnection = Nothing
  Private Shared objTransacao As MySqlTransaction = Nothing

  Public Shared Property ConnectionInstance()
    Get
      Try
        If (objConexao Is Nothing) Then objConexao = New MySqlConnection(CONNSTRING)
        If objConexao.State = ConnectionState.Closed Then objConexao.Open()
      Catch ex As ApplicationException
        Throw New ApplicationException("extranet.DA.BancoDeDados.ConnectionInstance(): " & ex.Message)
      End Try
      Return objConexao
    End Get
    Set(ByVal value)
      objConexao = value
    End Set
  End Property

  Public Shared Property TransactionInstance()
    Get
      Return objTransacao
    End Get
    Set(ByVal value)
      objTransacao = value
    End Set
  End Property

  Public Sub InicializarConexao()
    Try
      If objConexao Is Nothing Then objConexao = New MySqlConnection(CONNSTRING)
      If objConexao.State = ConnectionState.Closed Then objConexao.Open()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.BancoDeDados.InicializarConexao(): " & ex.Message)
    End Try
  End Sub

  Public Sub InicializarTransacao()
    'Dim booConexaoOpen As Boolean
    Try
      If objConexao Is Nothing Then objConexao = New MySqlConnection(CONNSTRING)
      'booConexaoOpen = Not ConnectionState.Closed
      'If booConexaoOpen Then objConexao.Close()
      objTransacao = objConexao.BeginTransaction(IsolationLevel.ReadCommitted)
      'If booConexaoOpen Then objConexao.Open()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.BancoDeDados.InicializarConexao(): " & ex.Message)
    End Try
  End Sub

  Public Sub FinalizarConexao()
    Try
      If objTransacao IsNot Nothing Then objTransacao.Dispose()
      If objConexao IsNot Nothing AndAlso Not objConexao.State = ConnectionState.Closed Then objConexao.Close()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.BancoDeDados.FinalizarConexao(): " & ex.Message)
    End Try
  End Sub

  Public Function ExecutaQuery(ByVal strComando As String) As DataSet
    Dim objDS As New DataSet
    Try
      InicializarConexao()
      Try
        Dim objDA As New MySqlDataAdapter
        objDA.SelectCommand = New MySqlCommand(strComando, objConexao)
        objDA.Fill(objDS)
      Catch ex As ApplicationException
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.BancoDeDados.ExecutaQuery(): " & ex.Message)
    End Try
    Return objDS
  End Function

  Public Sub ExecutaNonQuery(ByVal strComando As String)
    Try
      InicializarConexao()
      Try
        Dim objComando As New MySqlCommand(strComando, objConexao)
        objComando.ExecuteNonQuery()
      Catch ex As ApplicationException
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.BancoDeDados.ExecutaNonQuery(): " & ex.Message)
    End Try
  End Sub

  Public Function ExecutaEscalar(ByVal strComando As String) As String
    Try
      InicializarConexao()
      Try
        If Not strComando.EndsWith(";") Then strComando &= ";"
        Dim objComando As New MySqlCommand(strComando & " SELECT LAST_INSERT_ID();", objConexao)
        Return objComando.ExecuteScalar()
      Catch ex As ApplicationException
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.BancoDeDados.ExecutaNonQuery(): " & ex.Message)
    End Try
  End Function

  Public Sub Commit()
    Try
      If objTransacao IsNot Nothing Then objTransacao.Commit()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.BancoDeDados.Commit(): " & ex.Message)
    End Try
  End Sub

  Public Sub Rollback()
    Try
      If objTransacao IsNot Nothing Then objTransacao.Rollback()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.BancoDeDados.Commit(): " & ex.Message)
    End Try
  End Sub

  Public Sub duplicaAspasSimples(ByRef objData As Object, ByVal objType As System.Type)
    If objData IsNot Nothing Then
      Dim props As PropertyInfo() = objType.GetProperties()
      For Each p As PropertyInfo In props
        If p.PropertyType Is GetType(System.String) Then
          p.SetValue(objData, duplicaAspasSimples(p.GetValue(objData, Nothing)), Nothing)
        End If
      Next
    End If
  End Sub

  Public Function duplicaAspasSimples(ByVal strValor As String) As String
    If strValor Is Nothing Then strValor = ""
    Return strValor.Replace("'", "''")
  End Function

End Class
